package com.aeks.DB;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据的增删改查
 *
 */
public class OperateDB {
	private static List<Object> list = null;
	
	public static List<Object> getList() {
		return list;
	}
	
	/**
	 * 设置参数链表
	 *
	 */
	public static void setList(List<Object> paramList) {
		list = paramList; 
	}
	
	/**
	 * 查询数据
	 * 并返回查询结果
	 *
	 */
	
	public static List<Map> executeQuery(String sql) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		List<Map> list = new ArrayList<Map>();
		conn = ConnectDB.getConnection();
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if(rs != null) {
				ResultSetMetaData rsmd = rs.getMetaData();
			
				while(rs.next()) {
					Map<String, Object> map = new HashMap<String, Object>();
					for(int i=1; i<=rsmd.getColumnCount(); i++) {
						map.put(rsmd.getColumnName(i), rs.getObject(i));
						//System.out.println(rsmd.getColumnName(i));
					}
					list.add(map);
				}
			}
		} catch (SQLException e) {
			System.out.println("query error");
			e.printStackTrace();
		} finally {
			close(rs);
			close(stmt);
			close(conn);
		}
		return list;
	}
	
	/**
	 * 插入数据
	 * 并返回插入是否成功
	 *
	 */
	public static boolean insert(String sql) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		conn = ConnectDB.getConnection();
		try {
			pstmt = conn.prepareStatement(sql);
			if(list.size() != 0) {				//参数链表不为空，则将参数设置到sql语句中
				for(int i=0; i<list.size(); i++) {
					pstmt.setObject(i+1, list.get(i));
				}
			}
			pstmt.execute();
			return true;
		} catch (SQLException e) {
			System.out.println("insert error");
			e.printStackTrace();
			return false;
		} finally {
			close(pstmt);
			close(conn);
		}
	}
	
	/**
	 * 得到插入数据中自动生成的值
	 *
	 */
	public static int getGeneratedKey(String sql) {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		
		conn = ConnectDB.getConnection();
		try {
			pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			if(list.size() != 0) {
				for(int i=0; i<list.size(); i++) {
					pstmt.setObject(i+1, list.get(i));
				}
			}
			pstmt.execute();
			rs = pstmt.getGeneratedKeys();
			rs.next();
			int n = rs.getInt(1);
			return n;
		} catch (SQLException e) {
			System.out.println("insert_get error");
			e.printStackTrace();
			return 0;
		} finally {
			close(rs);
			close(pstmt);
			close(conn);
		}
		
	}
	
	/**
	 * 删除、更新数据
	 *返回影响数据的行数
	 *
	 */
	public static int update(String sql) {
		Connection conn = null;
		Statement stmt = null;
		
		conn = ConnectDB.getConnection();
		try {
			stmt = conn.createStatement();
			int n = stmt.executeUpdate(sql);
			return n;
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally {
			close(stmt);
			close(conn);
		}
		
	}
	
	public static void close(ResultSet rs) {
		if(rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs = null;
		}
	}
	
	public static void close(Statement stmt) {
		if(stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			stmt = null;
		}
	}
	
	public static void close(Connection conn) {
		if(conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conn = null;
		}
	}
}

